package tabelas.controle.Uf;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import persistence.HibernateUtil;

public class UfDAOImp implements UfDAO {
	private Session session 		= null;
	private Transaction transaction = null;
	
	public void setSessao(Session session){
		this.session = session;
	}
	@Override
	public boolean inseriruf(UF uf) {
		boolean retorno = false;
		try {
			this.session 		= HibernateUtil.getSessionFactory().openSession();
			this.transaction	= this.session.beginTransaction();
			uf.setDtinclusao(new Date());
			uf.setUsuarioinclusao("root");
			this.session.persist(uf);
			this.transaction.commit();
			retorno = true;
		} catch (Exception e) {
			this.transaction.rollback();
			e.printStackTrace();
		}finally{
			this.session.close();
		}
		return retorno;
	}

	@Override
	public boolean alteraruf(UF uf) {
		boolean retorno = false;
		try {
			this.session		= HibernateUtil.getSessionFactory().openSession();
			this.transaction	= this.session.beginTransaction();
			uf.setDtalteracao(new Date());
			uf.setUsuarioalteracao("root");
			this.session.update(uf);
			this.transaction.commit();
			retorno = true;
		} catch (Exception e) {
			this.transaction.rollback();
			e.printStackTrace();
		}finally{
			this.session.close();
		}
		return retorno;
	}

	@Override
	public boolean excluiruf(UF uf) {
		boolean retorno = false;
		try {
			this.session 		= HibernateUtil.getSessionFactory().openSession();
			this.transaction 	= this.session.beginTransaction();
			this.session.delete(this.session.get(UF.class, uf.getIduf()));
			this.transaction.commit();
			retorno = true;
		} catch (Exception e) {
			this.transaction.rollback();
			e.printStackTrace();
		}finally{
			this.session.close();
		}
		return retorno;
	}
	@Override
	public UF listarUF(UF uf) {
		// TODO Auto-generated method stub
		return null;
	}
	
	@Override
	@SuppressWarnings("unchecked")
	public List<String> ListaUFC(String nmuf){
	        List<String> list = new ArrayList<String>();
	        List<String> result = new ArrayList<String>();
	        ResultSet valuetpservico = null;
	        try {
	        	this.session = HibernateUtil.getSessionFactory().openSession();
	            this.transaction = this.session.beginTransaction();
	            
	            list = this.session.createQuery("SELECT NMUF FROM UF WHERE (NMUF like '%" +nmuf
	                   + "%') or (SGUF like '%" + nmuf + "%') ORDER BY NMUF ASC;").list();
	            String snmuf = "";
	            valuetpservico = (ResultSet) list;
	            valuetpservico.first();
	            while (!valuetpservico.isAfterLast()) {
	            	snmuf = valuetpservico.getString("NMUF");
	                result.add(snmuf);
	                //esult.add(cdmuf+" | "+snmuf);
	                valuetpservico.next();
	            }
	                        
	        } catch (Exception e) {
	        	e.printStackTrace();
	        }finally{
	        	this.session.close();
	        }
	        return result;
	}
	
	@SuppressWarnings("unchecked")
	public List<UF> localizaUFSelect(Integer sidUF){
		List<UF> list = null;
		try {
			this.session = HibernateUtil.getSessionFactory().openSession();
			this.transaction = this.session.beginTransaction();
			Query query = session.createQuery("from UF where idUF = "+sidUF);
			list = query.list();
			this.transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			this.session.close();
		}
		return list;
	}
	
	@Override
	public List<String> ListaUF(){
	        List<String> list = new ArrayList<String>();
	        List<String> result = new ArrayList<String>();
	        String sql = "SELECT CDUF, NMUF FROM UF;";
	        String snmuf = "";
	        String cdmuf = "";
	        try {
	            //this.session = HibernateUtil.getSessionFactory().openSession();
	            //this.transaction = this.session.beginTransaction();
	            //list = this.session.createQuery("SELECT NMUF FROM UF WHERE (NMUF like '%" +nmuf
	            	//              + "%') or (SGUF like '%" + nmuf + "%') ORDER BY NMUF ASC;").list();
	            //java.sql.Statement stmt = (Statement) this.session.getSessionFactory().openSession();
	        	
	        	this.session = HibernateUtil.getSessionFactory().openSession();
                this.transaction = this.session.beginTransaction();
	            //java.sql.Statement stmt = (Statement) this.session; 
	            ResultSet valuetpservico = null;
                valuetpservico = (ResultSet) this.session.createSQLQuery(sql);
	            this.transaction.commit();
	            valuetpservico.first();
	            while (!valuetpservico.isAfterLast()) {
	            	snmuf = valuetpservico.getString("NMUF");
	                //result.add(snmuf);
	                result.add(cdmuf+" | "+snmuf);
	                valuetpservico.next();
	            }
	                        
	        } catch (Exception e) {
	        	e.printStackTrace();
	        }finally{	        	
	            this.session.close();
	        }
	        return list;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<UF> listarTUF() {
		List<UF> list = null;
		try {
			this.session 		= HibernateUtil.getSessionFactory().openSession();
			this.transaction	= this.session.beginTransaction();
			Criteria sql = this.session.createCriteria(UF.class);
			list = sql.list();
			this.transaction.commit();
		} catch (Exception e) {
			this.transaction.rollback();
			e.printStackTrace();
		}finally{
			this.session.close();
		}
		return list;
	}

}
